How to detect fragmentation on indexes

It's important to analyze the index to determine the degree of fragmentation before you decide which defragmentation method to use, or if it's even necessary to run defragmentation. The system function sys.dm_db_index_physical_stats helps you detect fragmentation in a specific index, all indexes on a table or indexed view, or all indexes in a database. You can use this function to analyze fragmentation either through N4 or by executing a script.

To analyze fragmentation through N4:

1. In N4 open the App Indexes view (on page 1) (Administration DBA  App Indexes).

2. Select Actions Gather fragmentation Info. (This option is available only with SQL Server.)

3. Choose Yes to proceed.

This option is convenient to do and gives a better view of the output.

The results are displayed in the App Indexes view like this:

To analyze fragmentation with a script:

1. Execute the following script.

DECLARE @db_name NVARCHAR(128)

SET @db_name='DB NAME HERE'

SELECT s.name AS SchemaName ,

t.name AS TableName ,

t.object_id ,i.name AS IndexName,

i.index_id ,

x.page_count,

x.avg_fragmentation_in_percent ,

x.avg_page_space_used_in_percent ,

i.type_desc

FROM sys.dm_db_index_physical_stats(DB_ID(@db_name), NULL, NULL, NULL, 'SAMPLED') x

INNER JOIN sys.tables t ON x.object_id = t.object_id

INNER JOIN sys.schemas s ON t.schema_id = s.schema_id

INNER JOIN sys.indexes i ON x.object_id = i.object_id

AND x.index_id = i.index_id WHERE x.index_id > 0

AND alloc_unit_type_desc = 'IN_ROW_DATA'

ORDER BY avg_fragmentation_in_percent DESC

The advantage of this approach is that you can stop collecting information in case it takes too long and affects the application performance.

The script generates the following results:

• avg_fragmentation_in_percent: The percent of logical fragmentation (out-of-order pages) in the index.

• fragment_count: The number of fragments (physically consecutive leaf pages) in the index.

• avg_fragment_size_in_pages: The average number of pages in one fragment in the index.

Analyze fragmentation during off-peak hours.